﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace student_management_system
{
    public class School
    {
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
    }

    public class Class
    {
        public int ClassId { get; set; }
        public string ClassName { get; set; }
        public int SchoolId { get; set; }
    }

    public class Student
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public int Age { get; set; }
        public string Gender { get; set; }
        public int ClassId { get; set; }
    }

    public class Logger
    {
        public void LogAction(string action)
        {
            string connectionString = "Data Source=StudentManagement.db";
            string query = "INSERT INTO Log (Timestamp, Action) VALUES (@Timestamp, @Action)";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Timestamp", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    command.Parameters.AddWithValue("@Action", action);

                    command.ExecuteNonQuery();
                }
            }
        }

        public DataTable GetLogs()
        {
            DataTable logs = new DataTable();
            string connectionString = "Data Source=StudentManagement.db";
            string query = "SELECT * FROM Log";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    adapter.Fill(logs);
                }
            }

            return logs;
        }
    }

    public class StudentManagementSystem
    {
        private Logger logger;

        public StudentManagementSystem()
        {
            logger = new Logger();
            CreateTablesIfNotExists();
        }


        private void CreateTablesIfNotExists()
        {
            string connectionString = "Data Source=StudentManagement.db";
            string query = @"
        CREATE TABLE IF NOT EXISTS School (
            SchoolId INTEGER PRIMARY KEY AUTOINCREMENT,
            SchoolName TEXT
        );

        CREATE TABLE IF NOT EXISTS Class (
            ClassId INTEGER PRIMARY KEY AUTOINCREMENT,
            ClassName TEXT,
            SchoolId INTEGER
        );

        CREATE TABLE IF NOT EXISTS Student (
            StudentId INTEGER PRIMARY KEY AUTOINCREMENT,
            StudentName TEXT,
            Age INTEGER,
            Gender TEXT,
            ClassId INTEGER
        );

        CREATE TABLE IF NOT EXISTS Log (
            LogId INTEGER PRIMARY KEY AUTOINCREMENT,
            Timestamp TEXT,
            Action TEXT
        );
    ";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public void CreateSchool(School school)
        {
            string connectionString = "Data Source=StudentManagement.db";
            string query = "INSERT INTO School (SchoolName) VALUES (@SchoolName)";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@SchoolName", school.SchoolName);

                    command.ExecuteNonQuery();
                }
            }

            logger.LogAction("Created school: " + school.SchoolName);
        }

        public void CreateClass(Class _class)
        {
            string connectionString = "Data Source=StudentManagement.db";
            string query = "INSERT INTO Class (ClassName, SchoolId) VALUES (@ClassName, @SchoolId)";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@ClassName", _class.ClassName);
                    command.Parameters.AddWithValue("@SchoolId", _class.SchoolId);

                    command.ExecuteNonQuery();
                }
            }

            logger.LogAction("Created class: " + _class.ClassName);
        }
        public void CreateStudent(Student student)
        {
            string connectionString = "Data Source=StudentManagement.db";
            string query = "INSERT INTO Student (StudentName, Age, Gender, ClassId) " +
                           "VALUES (@StudentName, @Age, @Gender, @ClassId)";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@StudentName", student.StudentName);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@Gender", student.Gender);
                    command.Parameters.AddWithValue("@ClassId", student.ClassId);

                    command.ExecuteNonQuery();
                }
            }

            logger.LogAction("Created student: " + student.StudentName);
        }

        public DataTable GetSchools()
        {
            DataTable schools = new DataTable();
            string connectionString = "Data Source=StudentManagement.db";
            string query = "SELECT * FROM School";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    adapter.Fill(schools);
                }
            }

            return schools;
        }

        public DataTable GetClasses()
        {
            DataTable classes = new DataTable();
            string connectionString = "Data Source=StudentManagement.db";
            string query = "SELECT * FROM Class";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    adapter.Fill(classes);
                }
            }

            return classes;
        }
        public DataTable GetStudents()
        {
            DataTable students = new DataTable();
            string connectionString = "Data Source=StudentManagement.db";
            string query = "SELECT * FROM Student";

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection))
                {
                    adapter.Fill(students);
                }
            }

            return students;
        }
    }
    internal class Program
    {
        static void Main(string[] args)
        {
            StudentManagementSystem sms = new StudentManagementSystem();

            // 创建学校
            School school = new School
            {
                SchoolName = "ABC School"
            };
            sms.CreateSchool(school);

            // 创建班级
            Class _class = new Class
            {
                ClassName = "Class A",
                SchoolId = 1
            };
            sms.CreateClass(_class);

            // 创建学生
            Student student = new Student
            {
                StudentName = "John Doe",
                Age = 15,
                Gender = "Male",
                ClassId = 1
            };
            sms.CreateStudent(student);

            // 获取学校列表
            DataTable schools = sms.GetSchools();
            Console.WriteLine("Schools:");
            foreach (DataRow row in schools.Rows)
            {
                Console.WriteLine(row["SchoolId"] + ": " + row["SchoolName"]);
            }
            // 获取班级列表
            DataTable classes = sms.GetClasses();
            Console.WriteLine("Classes:");
            foreach (DataRow row in classes.Rows)
            {
                Console.WriteLine(row["ClassId"] + ": " + row["ClassName"] + " (SchoolId: " + row["SchoolId"] + ")");
            }

            // 获取学生列表
            DataTable students = sms.GetStudents();
            Console.WriteLine("Students:");
            foreach (DataRow row in students.Rows)
            {
                Console.WriteLine(row["StudentId"] + ": " + row["StudentName"] + " (Age: " + row["Age"] + ", Gender: " + row["Gender"] + ", ClassId: " + row["ClassId"] + ")");
            }

            // 获取日志记录
            Logger logger = new Logger();
            DataTable logs = logger.GetLogs();
            Console.WriteLine("Logs:");
            foreach (DataRow row in logs.Rows)
            {
                Console.WriteLine(row["LogId"] + ": " + row["Timestamp"] + " - " + row["Action"]);
            }

            Console.ReadLine();
        }
    }
}
